DROP DATABASE  IF EXISTS `pet_shop`;
CREATE DATABASE IF  NOT EXISTS  `pet_shop` DEFAULT  CHARACTER SET 'utf8';
USE `pet_shop`;

-- ## 类别表：- id- 大小- 类目- 创建时间
create table category(
category_id int primary key auto_increment COMMENT '种类编号', 
category  VARCHAR (32) NOT NULL COMMENT '动物品种',
deleted tinyint NOT NULL default 0 COMMENT '是否逻辑删除，逻辑已删除值1，逻辑未删除0',
create_time datetime NOT NULL COMMENT '创建时间'
)ENGINE=InnoDB AUTO_INCREMENT=1;
BEGIN;
INSERT INTO `category` VALUES 
(null, '狗狗',0,  "2021-06-08 23:53:17"), 
(null, '喵喵', 0,"2021-06-08 23:53:17"), 
(null, '哼哼', 0, "2021-06-08 23:53:17"), 
(null, '唧唧',0,"2021-06-08 23:53:17"), 
(null, '咕咕',0, "2021-06-08 23:53:17");
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;

create table good(
good_id int primary key auto_increment COMMENT '商品编号', 
good_name  VARCHAR (255) NOT NULL COMMENT '商品名',
category  int NOT NULL COMMENT '动物类别',
price decimal(18,2) NOT NULL COMMENT '价格',
is_missing boolean COMMENT '是否流浪',
count int NOT NULL COMMENT '数量', 
introduction text(2555) NOT NULL COMMENT '介绍',
image_count  int COMMENT '图片数量', 
image_path varchar(2555) COMMENT '图片地址', 
is_hot boolean COMMENT '是否热销',
create_time datetime NOT NULL COMMENT '创建时间',
update_time datetime NOT NULL COMMENT '修改时间', 
deleted tinyint NOT NULL default 0 COMMENT '是否逻辑删除，逻辑已删除值1，逻辑未删除0',
FOREIGN KEY(category) REFERENCES category(category_id)
)ENGINE=InnoDB AUTO_INCREMENT=1;
BEGIN;
INSERT INTO `good` VALUES
(null, '【新品】土狗纯种中华田园犬狗狗', 1,"2455.55", false,1,"只看门，干别的是另外的价格",3,"/images/default/1.jpg;/images/default/2.jpg;/images/default/3.jpg",false,"2021-06-08 23:53:17","2021-06-08 23:53:17",0), 
(null, '【热销】看门狗看我我就咬你超凶', 1,"10.5", false,1,"性格乖巧懂事，不乱拉屎，只在自己家拉屎",3,"/images/default/1.jpg;/images/default/2.jpg;/images/default/3.jpg",true,"2021-06-08 23:53:17","2021-06-08 23:53:17",0), 
(null, '【热销】蓝色布偶猫毛很蓬松', 2,"2455.55", false,1,"性格乖巧懂事，是一只萌猫猫",3,"/images/cat/2206930661110.jpg;/images/cat/2207299952661.png;/images/cat/2209800568584.jpg",true,"2021-06-08 23:53:17","2021-06-08 23:53:17",0), 
(null, '【热销】高贵冷艳波斯猫', 2,"2255.55", false,1,"外表冷漠心底温柔",3,"/images/default/1.jpg;/images/default/2.jpg;/images/default/3.jpg",true,"2021-06-08 23:53:17","2021-06-08 23:53:17",0),  
(null, '【热销】会说人话高数很6的拉布拉多狗狗', 1,"5000", false,1,"性格乖巧懂事，比别的拉布拉多矮一点",3,"/images/default/1.jpg;/images/default/2.jpg;/images/default/3.jpg",true,"2021-06-08 23:53:17","2021-06-08 23:53:17",0), 
(null, '【热销】会讲段子性格幽默的博美狗狗', 1,"10000", false,1,"性格乖巧懂事，不乱拉屎，会擦屁股",3,"/images/default/1.jpg;/images/default/2.jpg;/images/default/3.jpg",true,"2021-06-08 23:53:17","2021-06-08 23:53:17",0);
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
BEGIN;
INSERT INTO `good` VALUES
(null, '【新品】英短蓝白蓝猫折耳布偶猫矮脚美短金银渐层猫咪活物小猫幼猫宠物猫', 2,"2455.55", false,1,"性格乖巧懂事，是一只萌猫猫",3,"/images/cat/2206930661110.jpg;/images/cat/2207299952661.png;/images/cat/2209800568584.jpg",false,"2021-06-08 23:53:17","2021-06-08 23:53:17",0), 
(null, '【新品】英短蓝猫幼猫折耳蓝白银渐层短腿猫咪活物小猫咪美短猫幼崽宠物猫', 2,"2255.55", false,1,"外表冷漠心底温柔",3,"/images/default/1.jpg;/images/default/2.jpg;/images/default/3.jpg",false,"2021-06-08 23:53:17","2021-06-08 23:53:17",0),  
(null, '【新品】会说人话数学很6腿很香的拉布拉多狗狗', 1,"5000", false,1,"性格乖巧懂事，比别的拉布拉多矮一点",3,"/images/default/1.jpg;/images/default/2.jpg;/images/default/3.jpg",false,"2021-06-08 23:53:17","2021-06-08 23:53:17",0), 
(null, '【新品】个头小小单脾气超暴躁的泰迪狗狗', 1,"5000", false,1,"性格乖巧懂事，比别的拉布拉多矮一点",3,"/images/default/1.jpg;/images/default/2.jpg;/images/default/3.jpg",false,"2021-06-08 23:53:17","2021-06-08 23:53:17",0), 
(null, '【新品】狗狗', 1,"5000", false,1,"性格乖巧懂事，比别的拉布拉多矮一点",3,"/images/default/1.jpg;/images/default/2.jpg;/images/default/3.jpg",false,"2021-06-08 23:53:17","2021-06-08 23:53:17",0), 
(null, '【新品】个头小小单脾气超暴躁的泰迪狗狗', 1,"5000", false,1,"性格乖巧懂事，比别的拉布拉多矮一点",3,"/images/default/1.jpg;/images/default/2.jpg;/images/default/3.jpg",false,"2021-06-08 23:53:17","2021-06-08 23:53:17",0), 
(null, '【新品】个头小小单脾气超暴躁的泰迪狗狗', 1,"5000", false,1,"性格乖巧懂事，比别的拉布拉多矮一点",3,"/images/default/1.jpg;/images/default/2.jpg;/images/default/3.jpg",false,"2021-06-08 23:53:17","2021-06-08 23:53:17",0), 
(null, '【新品】会讲段子性格幽默的博美狗狗', 1,"10000", false,1,"性格乖巧懂事，不乱拉屎，会擦屁股",3,"/images/default/1.jpg;/images/default/2.jpg;/images/default/3.jpg",false,"2021-06-08 23:53:17","2021-06-08 23:53:17",0);
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
BEGIN;
INSERT INTO `good` VALUES
(null, '【❤】中华田园猫', 2,"1", true,1,"性格乖巧懂事，是一只萌猫猫",3,"/images/default/1.jpg;/images/default/2.jpg;/images/default/3.jpg",false,"2021-06-08 23:53:17","2021-06-08 23:53:17",0), 
(null, '【❤】土气但温柔的田园犬狗狗', 1,"1", true,1,"外表冷漠心底温柔",3,"/images/default/1.jpg;/images/default/2.jpg;/images/default/3.jpg",false,"2021-06-08 23:53:17","2021-06-08 23:53:17",0),  
(null, '【❤】家住户县编程超6的金毛狗', 1,"1", true,1,"外表冷漠心底温柔",3,"/images/default/1.jpg;/images/default/2.jpg;/images/default/3.jpg",false,"2021-06-08 23:53:17","2021-06-08 23:53:17",0),  
(null, '【❤】安静乖巧的小菊喵', 2,"1", true,1,"性格乖巧懂事，比别的拉布拉多矮一点",3,"/images/default/1.jpg;/images/default/2.jpg;/images/default/3.jpg",false,"2021-06-08 23:53:17","2021-06-08 23:53:17",0), 
(null, '【❤】翅膀受伤的小麻雀', 5,"1", true,1,"性格乖巧懂事，不乱拉屎，会擦屁股",3,"/images/default/1.jpg;/images/default/2.jpg;/images/default/3.jpg",false,"2021-06-08 23:53:17","2021-06-08 23:53:17",0);
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
-- ## 用户：-- - id-- - 用户名-- - 手机号码《》 -- - 密码-- - 是否为管理员-- - 创建时间

create table customer(
customer_id int primary key auto_increment COMMENT '用户id', 
customer_name  VARCHAR (64) NOT NULL COMMENT '用户名',
phone  char(11) NOT NULL unique  COMMENT '手机号码',
`password` varchar(32) NOT NULL COMMENT '密码',
is_manager boolean NOT NULL COMMENT '是否为管理员', 
create_time datetime NOT NULL COMMENT '注册时间',
deleted tinyint NOT NULL default 0 COMMENT '是否逻辑删除，逻辑已删除值1，逻辑未删除0',
update_time datetime NOT NULL COMMENT '修改时间'
)ENGINE=InnoDB AUTO_INCREMENT=1;
BEGIN;
INSERT INTO `customer` VALUES 
(null, 'admin', 12345678910,"asdfghjk", true,"2021-06-08 23:53:17",0,"2021-06-08 23:53:17"), 
(null, '苏苏', 12345678911,"123", false,"2021-06-08 23:53:17",0,"2021-06-08 23:53:17"), 
(null, '佩奇', 15829509072,"katinkhosszu", false,"2021-06-08 23:53:17",0,"2021-06-08 23:53:17"), 
(null, '瑶瑶', 12345678913,"asdfghjk", false,"2021-06-08 23:53:17",0,"2021-06-08 23:53:17"), 
(null, '小花', 12345678914,"asdfghjk", false,"2021-06-08 23:53:17",0,"2021-06-08 23:53:17"), 
(null, '大柱', 12345678915,"asdfghjk", false,"2021-06-08 23:53:17",0,"2021-06-08 23:53:17"), 
(null, '朱朱', 12345678916,"asdfghjk", false,"2021-06-08 23:53:17",0,"2021-06-08 23:53:17");
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;


-- ## 收货地址表：-- - 用户ID-- - 收货人-- - 收货电话-- - 收货地址
create table address(
address_id int primary key auto_increment COMMENT '地址id',
customer int NOT NULL COMMENT '用户id', 
address_name  VARCHAR (64) NOT NULL COMMENT '地址',
`isdefault` int DEFAULT '0' COMMENT '是否是默认地址（1:是 0否）',
username  VARCHAR (64) NOT NULL COMMENT '收货人',
phone  char(11) NOT NULL COMMENT '手机号码',
create_time datetime NOT NULL COMMENT '创建时间',
deleted tinyint NOT NULL default 0 COMMENT '是否逻辑删除，逻辑已删除值1，逻辑未删除0',
update_time datetime NOT NULL COMMENT '修改时间',
FOREIGN KEY(customer) REFERENCES customer(customer_id)
)ENGINE=InnoDB AUTO_INCREMENT=1;
BEGIN;
INSERT INTO `address` VALUES 
(null, 1,"陕西省西安市长安区西安邮电大学", 1,"岳佳佳",12341002586,"2021-06-08 23:53:17",0,"2021-06-08 23:53:17"), 
(null, 1,"陕西省宝鸡市陈仓区主街道最大的下水道", 0,"佳狗",1234102586,"2021-06-08 23:53:17",0,"2021-06-08 23:53:17"), 
(null, 2,"陕西省西安市长安区西部大道最大的井盖", 0,"拒收邮政",1234102586,"2021-06-08 23:53:17",0,"2021-06-08 23:53:17"), 
(null, 2,"陕西省西安市长安区西安有点大学西游桥桥洞", 1,"屁股",1234102586,"2021-06-08 23:53:17",0,"2021-06-08 23:53:17"), 
(null, 3,"陕西省宝鸡市金台区最好看的桥洞", 1,"佩奇",15829509072,"2021-06-08 23:53:17",0,"2021-06-08 23:53:17"), 
(null, 1,"陕西省西安市长安区最高楼的厕所", 0,"小狗恋恋",1234102586,"2021-06-08 23:53:17",0,"2021-06-08 23:53:17");
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;


-- ## 订单表：-- - 订单id-- - 收货信息-- - 支付方式-- - 订单总价-- - 下单时间-- - 状态-- 
create table indent(
indent_id int primary key auto_increment  COMMENT '订单id', 
customer int NOT NULL  COMMENT '用户id', 
address int NOT NULL  COMMENT '地址id',
total_price  decimal NOT NULL  COMMENT '订单总价',
indent_status int NOT NULL  COMMENT '订单状态0代表未支付，1代表已支付配送中，2代表已完成',
create_time datetime NOT NULL  COMMENT '创建时间',
deleted tinyint NOT NULL default 0 COMMENT '是否逻辑删除，逻辑已删除值1，逻辑未删除0',
update_time datetime NOT NULL  COMMENT '修改时间',
FOREIGN KEY(customer) REFERENCES customer(customer_id),
FOREIGN KEY(address) REFERENCES address(address_id)
)ENGINE=InnoDB AUTO_INCREMENT=1;
BEGIN;
INSERT INTO `indent` VALUES 
(null, 5,1, 250,1,"2021-06-08 23:53:17",0,"2021-06-08 23:53:17"), 
(null, 4,1, 250,0,"2021-06-08 23:53:17",0,"2021-06-08 23:53:17"), 
(null, 2,1, 250,2,"2021-06-08 23:53:17",0,"2021-06-08 23:53:17"), 
(null, 1,2, 4711.1,1,"2021-06-08 23:53:17",0,"2021-06-08 23:53:17");
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;


-- ## 订单项表：-- - 订单项id-- - 订单id《-- 付款时-- - 商品id-- - 价格-- - 数量-- - 创建时间
create table indent_item(
indent_item_id int primary key auto_increment COMMENT '订单项id',
indent int NOT NULL COMMENT '订单id', 
good int NOT NULL COMMENT '商品id', 
total_price  decimal NOT NULL COMMENT '订单项总价',
univalence decimal NOT NULL COMMENT '订单项单价',
create_time datetime NOT NULL COMMENT '创建时间',
amount int NOT NULL COMMENT '数量',
pay_time datetime NOT NULL COMMENT '付款时间',
FOREIGN KEY(good) REFERENCES good(good_id),
FOREIGN KEY(indent) REFERENCES indent(indent_id)
)ENGINE=InnoDB AUTO_INCREMENT=1;
BEGIN;
INSERT INTO `indent_item` VALUES 
(null, 1,5, "250","250","2021-06-08 23:53:17",1,"2021-06-08 23:53:17"), 
(null, 2,3, "2455.55","2455.55","2021-06-08 23:53:17",1,"2021-06-08 23:53:17"), 
(null, 3,6, "2455.55","2455.55","2021-06-08 23:53:17",1,"2021-06-08 23:53:17"), 
(null, 3,8, "2455.55","2455.55","2021-06-08 23:53:17",1,"2021-06-08 23:53:17"), 
(null, 2,4, "2255.55","2255.55","2021-06-08 23:53:17",1,"2021-06-08 23:53:17");
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;


-- ## 购物车表：
create table cart(
cart_id int primary key auto_increment COMMENT '购物车id',
customer int NOT NULL COMMENT '用户id', 
good int NOT NULL COMMENT '商品id', 
create_time datetime NOT NULL COMMENT '创建时间',
amount int NOT NULL COMMENT '数量',
FOREIGN KEY(customer) REFERENCES customer(customer_id),
FOREIGN KEY(good) REFERENCES good(good_id)
)ENGINE=InnoDB AUTO_INCREMENT=1;
BEGIN;
INSERT INTO `cart` VALUES 
(null, 1,6,"2021-06-08 23:53:17",1), 
(null, 2,6,"2021-06-08 23:53:17",1), 
(null, 2,8,"2021-06-08 23:53:17",1), 
-- (null, 2,10,"2021-06-08 23:53:17",1), 
(null, 3,7,"2021-06-08 23:53:17",1);
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;

## 收藏夹表：
create table favorite(
favorite_id int primary key auto_increment COMMENT '收藏夹id',
customer int NOT NULL COMMENT '用户id', 
good int NOT NULL COMMENT '商品id', 
create_time datetime NOT NULL COMMENT '创建时间',#创建时间
initial_price decimal NOT NULL COMMENT '初始价格',
FOREIGN KEY(customer) REFERENCES customer(customer_id),
FOREIGN KEY(good) REFERENCES good(good_id)
)ENGINE=InnoDB AUTO_INCREMENT=1;
BEGIN;
INSERT INTO `favorite` VALUES 
(null, 1,7,"2021-06-08 23:53:17",10000), 
(null, 2,7,"2021-06-08 23:53:17",10000), 
(null, 3,5,"2021-06-08 23:53:17",250);
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;

